package com.doubleview.hutool;


import cn.hutool.core.convert.Convert;
import cn.hutool.core.map.MapUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.json.JSONUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.Data;
import org.junit.Test;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author huchengchao@kuaishou.com
 * Created on 2021-11-03
 */

public class ExcelUtils {

    @Test
    public void parseJbpExcel() {
        ExcelReader excelReader = ExcelUtil.getReader(new File("/Users/huchengchao/Downloads/brand.xlsx"), 0);
        List<Map<String, Object>> objectMap = excelReader.readAll();

        List<AgreementInfoPO> dataList = new ArrayList<>();
        for (Map<String, Object> map : objectMap) {
            AgreementInfoPO data = new AgreementInfoPO();
            data.setAgreementName(MapUtil.getStr(map, "协议名称"));
            data.setAgreementNo(MapUtil.getStr(map, "协议编号"));
            data.setAgreementType(1);
            data.setSignedMainName(MapUtil.getStr(map, "签约主体名称"));
            data.setSignedMainCertNo(MapUtil.getStr(map, "签约主体证照号", "1"));
            data.setEffectiveStartTime(MapUtil.getLong(map, "协议有效期起始日期"));
            data.setEffectiveEndTime(MapUtil.getLong(map, "协议有效期终止日期"));
            data.setOperatorEmail(MapUtil.getStr(map, "运营小二邮箱前缀"));
            data.setMasterSellerId(MapUtil.getLong(map, "协议主商户ID"));
            data.setSettlementBankName(MapUtil.getStr(map, "结算账户开户行"));
            data.setSettlementBankName(MapUtil.getStr(map, "结算账户开户行"));
            data.setSettlementBankNo(MapUtil.getStr(map, "开户行行号（需百度）"));
            data.setSettlementAccountName(MapUtil.getStr(map, "账户名（开户名称）"));
            data.setSettlementAccountNo(MapUtil.getStr(map, "银行账号"));
            String frequency = MapUtil.getStr(map, "结算频率");
            //按月跳过
            if (Convert.toInt(frequency.substring(0, 1)) == 1) {
                continue;
            }
            data.setSettlementFrequency(Convert.toInt(frequency.substring(0, 1)));
            data.setMaxReturnAmt(MapUtil.getLong(map, "最大返还金额（选填）") * 100);
            String toBusiness = MapUtil.getStr(map, "账户类型");
            data.setToBusiness(toBusiness.equals("对公") ? 1 : 2);
            data.setRewardId("JBP_BRAND_YEAR");
            data.setBrandIds((MapUtil.getStr(map, "品牌ID", "")));
            data.setStatus(2);

            GmvLevelConfigDO configDO = new GmvLevelConfigDO();
            configDO.setBrandSalesGmvLevel(parseGmvLevel(MapUtil.getStr(map, "品牌销售GMV档位")));
            configDO.setBrandSelfLiveGmvLevel(parseGmvLevel(MapUtil.getStr(map, "品牌自播GMV档位")));
            data.setGmvLevelConfig(JSONUtil.toJsonStr(configDO));

            data.setCreateOperator("huchengchao");
            data.setUpdateOperator("huchengchao");
            long now = System.currentTimeMillis();
            data.setCTime(now);
            data.setUTime(now);
            dataList.add(data);

            String sellerStr = MapUtil.getStr(map, "关联商户");
            List<String> sellerStrList = StrUtil.split(sellerStr, ",");
            List<AgreementSellerInfoPO> sellerInfoPOList = new ArrayList<>();
            for (String s : sellerStrList) {
                List<String> sellerList = StrUtil.split(s, "｜");
                AgreementSellerInfoPO agreementSellerInfoPO = new AgreementSellerInfoPO();
                agreementSellerInfoPO.setAgreementType(1);
                agreementSellerInfoPO.setSellerId(Convert.toLong(sellerList.get(0)));
                agreementSellerInfoPO.setEffectiveStartTime(Convert.toLong(sellerList.get(1)));
                agreementSellerInfoPO.setEffectiveEndTime(Convert.toLong(sellerList.get(2)));
                agreementSellerInfoPO.setCTime(now);
                agreementSellerInfoPO.setUTime(now);
                agreementSellerInfoPO.setStatus(1);
                agreementSellerInfoPO.setCreateOperator("huchengchao");
                agreementSellerInfoPO.setUpdateOperator("huchengchao");
                sellerInfoPOList.add(agreementSellerInfoPO);
            }
            data.setSellerList(sellerInfoPOList);
        }
        System.out.println(JSONUtil.toJsonStr(dataList));
    }

    @Test
    public void parseTntExcel() {
        ExcelReader excelReader = ExcelUtil.getReader(new File("/Users/huchengchao/Downloads/brand1.xlsx"), 1);
        List<Map<String, Object>> objectMap = excelReader.readAll();

        List<AgreementInfoPO> dataList = new ArrayList<>();
        for (Map<String, Object> map : objectMap) {
            AgreementInfoPO data = new AgreementInfoPO();
            data.setAgreementName(MapUtil.getStr(map, "协议名称"));
            data.setAgreementNo(MapUtil.getStr(map, "协议编号"));
            data.setAgreementType(2);
            data.setSignedMainName(MapUtil.getStr(map, "签约主体名称"));
            data.setSignedMainCertNo(MapUtil.getStr(map, "签约主体证照号", "1"));
            data.setEffectiveStartTime(MapUtil.getLong(map, "协议有效期起始日期"));
            data.setEffectiveEndTime(MapUtil.getLong(map, "协议有效期终止日期"));
            data.setOperatorEmail(MapUtil.getStr(map, "运营小二邮箱前缀"));
            data.setMasterSellerId(MapUtil.getLong(map, "协议主商户ID"));
            data.setSettlementBankName(MapUtil.getStr(map, "结算账户开户行"));
            data.setSettlementBankNo(MapUtil.getStr(map, "开户行行号"));
            data.setSettlementAccountName(MapUtil.getStr(map, "账户名"));
            data.setSettlementAccountNo(MapUtil.getStr(map, "账号"));
            String frequency = MapUtil.getStr(map, "结算频率");
            data.setSettlementFrequency(Convert.toInt(frequency.substring(0, 1)));
            data.setMaxReturnAmt(MapUtil.getLong(map, "最大返还金额（选填）", 0L) * 100);
            String toBusiness = MapUtil.getStr(map, "账户类型");
            data.setToBusiness(toBusiness.equals("对公") ? 1 : 2);
            data.setRewardId("TNT_ANCHOR_YEAR");
            data.setBrandIds((MapUtil.getStr(map, "品牌ID", "")));
            data.setStatus(2);

            GmvLevelConfigDO configDO = new GmvLevelConfigDO();
            configDO.setAnchorGmvLevel(parseGmvLevel(MapUtil.getStr(map, "主播销售GMV档位")));
            data.setGmvLevelConfig(JSONUtil.toJsonStr(configDO));

            data.setCreateOperator("huchengchao");
            data.setUpdateOperator("huchengchao");
            long now = System.currentTimeMillis();
            data.setCTime(now);
            data.setUTime(now);
            dataList.add(data);

            String sellerStr = MapUtil.getStr(map, "关联商户");
            List<String> sellerStrList = StrUtil.split(sellerStr, ",");
            List<AgreementSellerInfoPO> sellerInfoPOList = new ArrayList<>();
            for (String s : sellerStrList) {
                List<String> sellerList = StrUtil.split(s, "|");
                AgreementSellerInfoPO agreementSellerInfoPO = new AgreementSellerInfoPO();
                agreementSellerInfoPO.setAgreementType(2);
                agreementSellerInfoPO.setSellerId(Convert.toLong(sellerList.get(0)));
                agreementSellerInfoPO.setEffectiveStartTime(Convert.toLong(sellerList.get(1)));
                agreementSellerInfoPO.setEffectiveEndTime(Convert.toLong(sellerList.get(2)));
                agreementSellerInfoPO.setCTime(now);
                agreementSellerInfoPO.setUTime(now);
                agreementSellerInfoPO.setStatus(1);
                agreementSellerInfoPO.setCreateOperator("huchengchao");
                agreementSellerInfoPO.setUpdateOperator("huchengchao");
                sellerInfoPOList.add(agreementSellerInfoPO);
            }
            data.setSellerList(sellerInfoPOList);
        }
        System.out.println(JSONUtil.toJsonStr(dataList));
    }

    private List<GmvLevelDO> parseGmvLevel(String gmvLevelStr) {
        List<GmvLevelDO> gmvLevelDOList = new ArrayList<>();

        List<String> levelConfigStrList = StrUtil.split(gmvLevelStr, ",");
        for (String s : levelConfigStrList) {
            List<String> levelConfigList = StrUtil.split(s, "|");
            GmvLevelDO levelDO = new GmvLevelDO();
            long low = Convert.toLong(levelConfigList.get(0));
            if (low <= 10000) {
                low = low * 10000;
            }
            levelDO.setLow(low * 100);
            levelDO.setHigh(Convert.toLong(levelConfigList.get(1)) * 100);
            levelDO.setRewardPercent(levelConfigList.get(2));
            gmvLevelDOList.add(levelDO);
        }
        if (gmvLevelDOList.get(0).getRewardPercent().equals("0")) {
            gmvLevelDOList.get(0).setLow(0);
        } else {
            GmvLevelDO levelDO = new GmvLevelDO();
            levelDO.setLow(0);
            levelDO.setHigh(gmvLevelDOList.get(0).getLow());
            levelDO.setRewardPercent("0");
            gmvLevelDOList.add(0, levelDO);
        }
        return gmvLevelDOList;
    }

    @Data
    public class AgreementInfoPO {
        private long id;
        private String agreementName;
        private String agreementNo;
        private int agreementType;
        private String signedMainName;
        private String signedMainCertNo;
        private long effectiveStartTime;
        private long effectiveEndTime;
        private String operatorEmail;
        private long masterSellerId;
        private String settlementBankName;
        private String settlementBankNo;
        private String settlementAccountName;
        private String settlementAccountNo;
        private int settlementFrequency;
        private long maxReturnAmt;
        private int toBusiness;

        private String rewardId;
        private String brandIds;
        private int status;
        private String gmvLevelConfig;
        private String payload;

        private String createOperator;
        private String updateOperator;

        private long cTime;
        private long uTime;

        private List<AgreementSellerInfoPO> sellerList;
    }

    @Data
    public class AgreementSellerInfoPO {
        private long id;
        private long agreementId;
        private int agreementType;

        private long sellerId;
        private String sellerName;
        private long effectiveStartTime;
        private long effectiveEndTime;
        private int status;

        private String payload;
        private String createOperator;
        private String updateOperator;
        private long cTime;
        private long uTime;
    }

    @Data
    public class GmvLevelDO {
        private long low;
        private long high;
        private String rewardPercent;
    }

    @Data
    public class GmvLevelConfigDO {
        //品牌年框-销售任务gmv
        private List<GmvLevelDO> brandSalesGmvLevel;
        //品牌年框-自播任务gmv打标档位
        private List<GmvLevelDO> brandSelfLiveGmvLevel;
        //主播年框-主播销售gmv档位配置
        private List<GmvLevelDO> anchorGmvLevel;
    }
}
